/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package onpu_dms_xls.kernel.common.XLS;

import onpu_dms_xls.kernel.common.logAndDebug.Debug;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

import onpu_dms_xls.exceptions.MyException;
import onpu_dms_xls.exceptions.MyXLSException;
import onpu_dms_xls.kernel.common.Util;
import onpu_dms_xls.kernel.common.VariableDataTypes;
import onpu_dms_xls.kernel.common.VariableDeclaration;
import onpu_dms_xls.kernel.common.logAndDebug.LoggingFacade;
import org.apache.log4j.Logger;

/**
 *
 * @author snbm
 */
public class XLS_Variables {
    

    private ArrayList<XLS_Variable> varArray;

    private boolean fileForWritingOpened;
    private InputStream inp;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;

    public XLS_Variables(File XLSFile) throws MyXLSException {
        this.fileForWritingOpened = false;
        try {
            inp = new FileInputStream(XLSFile.getAbsoluteFile());
            wb = new HSSFWorkbook(inp);
            sheet = wb.getSheetAt(0);
        } catch (IOException e) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "XLS_Variables",
                    "error while opening XLS result file",
                    e.getMessage());
        }
        varArray = new ArrayList<XLS_Variable>();
        //full lines iteration loop for variables search
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    String cellVal = cell.getRichStringCellValue().getString();
                    if (cellVal.startsWith("$")) {
                        //so we met a variable.
                        XLS_Variable newVar = new XLS_Variable(cell.getColumnIndex(), row.getRowNum(), cellVal.substring(1));
                        varArray.add(newVar);
                    }
                }
            }
        }

        try {
            FileOutputStream fileOut = new FileOutputStream(XLSFile.getAbsolutePath());
            wb.write(fileOut);
            fileOut.close();
            inp.close();
        } catch (Exception e) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "XLS_Variables",
                    "error while inp.close();",
                    "");
        }

    }

    private void openFileForWriting(File XLSFile) throws MyXLSException {
        try {
            inp = new FileInputStream(XLSFile.getAbsoluteFile());
            wb = new HSSFWorkbook(inp);
            sheet = wb.getSheetAt(0);
        } catch (IOException e) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "XLS_Variables",
                    "error while opening XLS result file",
                    e.getMessage());
        }

        this.fileForWritingOpened = true;
    }

    private void closeFileAfterWriting(File XLSFile) throws MyXLSException {
        if (this.fileForWritingOpened == false){
            this.openFileForWriting(XLSFile);
        }
        this.fileForWritingOpened = false;
        try {
            FileOutputStream fileOut = new FileOutputStream(XLSFile.getAbsolutePath());
            wb.write(fileOut);
            fileOut.close();
            inp.close();
        } catch (Exception e) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "writeVariableToFile",
                    "error while inp.close();",
                    e.getMessage());
        }
    }

    /**
    only 2 data types are worked through!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
     */
    public void writeVariableToFile(File XLSFile, VariableDeclaration varDec, String variableValue)
            throws MyXLSException {

        if (!this.fileForWritingOpened) {
            this.openFileForWriting(XLSFile);
        }

        //find variable
        int row = -1;
        int col = -1;

        for (XLS_Variable XLSV : varArray) {
            LoggingFacade.trace.info("writeVariableToFile, name from XLS: " + XLSV.getName() + " name from XML: " + varDec.getVarName());
            if (XLSV.getName().equals(varDec.getVarName())) {
                row = XLSV.getRow();
                col = XLSV.getCol();
                break;
            }
        }

        LoggingFacade.trace.info("found variable " + varDec.getVarName() + " in XLS: row:" + row + " col:" + col);

        if (row == -1 || col == -1) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "writeVariableToFile",
                    "no such variable found in XLS, varName:" + varDec.getVarName(),
                    "");
        }

        //write to xls.
        Cell cell = null;
        try {
            cell = sheet.getRow(row).getCell(col);
            LoggingFacade.trace.info("cell value before setting:" + cell.getStringCellValue());
        } catch (Exception e) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "writeVariableToFile",
                    "error while opening cell: row:" + row + " cell: " + col,
                    e.getMessage());
        }

        int possibleQueryResult;
        if (varDec.getDataType() == VariableDataTypes.INT) {
            possibleQueryResult = Integer.parseInt(variableValue);
            LoggingFacade.trace.info("query result, parsed to int:" + possibleQueryResult + " for variable:" + varDec.getVarName());
            cell.setCellValue(possibleQueryResult);
            LoggingFacade.trace.info("wrote to XLS: " + cell.getNumericCellValue() + " from XML int variable: " + varDec.getVarName());

            return;
        }


        try {
            cell.setCellValue(variableValue);
        } catch (Exception e) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "writeVariableToFile",
                    "error while cell.setCellValue(variableValue); row:" + row + " cell: " + col,
                    e.getMessage());
        }

        LoggingFacade.trace.info("wrote to XLS: " + cell.getStringCellValue() + " from XML string variable: " + varDec.getVarName());

        try {
            LoggingFacade.trace.trace("1");
            FileOutputStream fileOut = new FileOutputStream(XLSFile.getAbsolutePath());
            LoggingFacade.trace.trace("2"+XLSFile.getAbsolutePath());
            wb.write(fileOut);
            LoggingFacade.trace.trace("3");
            fileOut.close();
            LoggingFacade.trace.trace("4");
            inp.close();
            LoggingFacade.trace.trace("5");
        } catch (Exception e) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "writeVariableToFile",
                    "error while inp.close();",
                    "" + cell);
        }

    }

    public void appendIterationRowsToXLS(File XLSFile, VariableDeclaration[] iterVarDecls)
            throws MyXLSException {

        //so we have in input an iteration row with variables names and values.
        //so let us just fill the XLS file with each variable from that row...

        //BUT it is important to copy current last iteration row in result file
        //copy it to one row lower...

        if (!this.fileForWritingOpened) {
            this.openFileForWriting(XLSFile);
        }


        //for this, we must find the row, in which the current marks table ends:
        int theRow = -1;

        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    String cellVal = cell.getRichStringCellValue().getString();
                    if (cellVal.startsWith("$" + iterVarDecls[0].getVarName())) {
                        LoggingFacade.trace.info("found a current last row variable:" + iterVarDecls[0].getVarName() + "in XLS: row:" + row.getRowNum() + " col:" + cell.getColumnIndex());
                        //so we met a variable.
                        theRow = row.getRowNum();
                    }
                }
            }
        }


        if (theRow == -1) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "appendIterationRowsToXLS",
                    "error while getting the , varName:" + iterVarDecls[0].getVarName(),
                    "");
        }
        LoggingFacade.trace.info("-------!!!!!!!!!!!copying row " + theRow + " to row " + (theRow + 1));
        Util.copyRow(wb, sheet, theRow, theRow + 1);


        //now, we write data from VariableDeclaration[] array of iter variables to the rowForCurrentEndOfIterationTable
        for (int i = 0; i < iterVarDecls.length; i++) {
            //other implementation of write variable to XLS file method + finding the variable in XLS

            //find variable
            int varRow = -1;
            int varCol = -1;

            one_time_for:
            {
                for (Row row : sheet) {
                    for (Cell cell : row) {
                        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                            String cellVal = cell.getRichStringCellValue().getString();
                            if (cellVal.startsWith("$" + iterVarDecls[i].getVarName())) {
                                LoggingFacade.trace.info("found a variable (in which i will write now):" + iterVarDecls[i].getVarName() + " in XLS: row:" + row.getRowNum() + " col:" + cell.getColumnIndex());
                                //so we met a variable.
                                varCol = cell.getColumnIndex();
                                varRow = row.getRowNum();
                                break one_time_for;
                            }
                        }
                    }
                }
            }


            if (varRow == -1 || varCol == -1) {
                throw new MyXLSException(
                        "XLS_Variables",
                        "appendIterationRowsToXLS",
                        "no such variable found in XLS, varName:" + iterVarDecls[i].getVarName(),
                        "");
            }

            //write to xls.
            Cell cell = null;
            try {
                cell = sheet.getRow(varRow).getCell(varCol);
                LoggingFacade.trace.info("cell value before setting:" + cell.getStringCellValue());
            } catch (Exception e) {
                throw new MyXLSException(
                        "XLS_Variables",
                        "writeVariableToFile",
                        "error while opening cell: row:" + varRow + " cell: " + varCol,
                        "");
            }
            
            cell.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            cell.getCellStyle().setBorderLeft(CellStyle.BORDER_THIN);
            cell.getCellStyle().setBorderRight(CellStyle.BORDER_THIN);
            cell.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
            
            int possibleQueryResult;
            if (iterVarDecls[i].getDataType() == VariableDataTypes.INT
                    && !iterVarDecls[i].getValue().equals("")) {
                possibleQueryResult = Integer.parseInt(iterVarDecls[i].getValue());
                LoggingFacade.trace.info("query result, parsed to int:" + possibleQueryResult + " for variable:" + iterVarDecls[i].getVarName());
                cell.setCellValue(possibleQueryResult);
                LoggingFacade.trace.info("wrote to XLS cell: row" + varRow + "col:" + varCol + " value: " + cell.getNumericCellValue() + " from XML int variable: " + iterVarDecls[i].getVarName());
                continue;
            }

            try {
                cell.setCellValue(iterVarDecls[i].getValue());
            } catch (Exception e) {
                throw new MyXLSException(
                        "XLS_Variables",
                        "writeVariableToFile",
                        "error while cell.setCellValue(variableValue); row:" + varRow + " cell: " + varCol,
                        e.getMessage());
            }

            LoggingFacade.trace.info("wrote to XLS cell: row" + varRow + "col:" + varCol + " value: " + cell.getStringCellValue() + " from XML string variable: " + iterVarDecls[i].getVarName());
        }


    }

    public void destroyLastIterationRowLeft(VariableDeclaration[] iterVarDecls) throws MyXLSException {


        //for this, we must find the row, in which the current marks table ends:
        int theRowNum = -1;

        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    String cellVal = cell.getRichStringCellValue().getString();
                    if (cellVal.startsWith("$" + iterVarDecls[0].getVarName())) {
                        LoggingFacade.trace.info("found a current last row variable:" + iterVarDecls[0].getVarName() + "in XLS: row:" + row.getRowNum() + " col:" + cell.getColumnIndex());
                        theRowNum = row.getRowNum();
                        break;
                    }
                }
            }
        }
        LoggingFacade.trace.info("*************deleting (last iterable) row:" + theRowNum);
        sheet.removeRow(sheet.getRow(theRowNum));

    }

    public void closeConnectionToXLSFile(File XLSFile) throws MyException {
        this.closeFileAfterWriting(XLSFile);
        LoggingFacade.trace.info("end of writing to XLS");
    }


    public XLS_Variable getVariableFromName(String varName)
            throws MyXLSException {
        int resultCount = 0;
        XLS_Variable res = null;
        for (XLS_Variable xlsv : varArray) {
            if (xlsv.getName().equals(varName)) {
                resultCount++;
                res = xlsv;
            }
        }
        if (resultCount != 1) {
            throw new MyXLSException("XLS_Variables", "getVariableFromName", "wrong amount of variables:", "amount: " + resultCount);
        }
        return res;
    }
}
